-- This scripts contains following function's definition:
-- =============================================================================
-- months_between(timestamp,timestamp)
-- sys_guid()
-- wm_concat(text)
-- nullif(anyelement,anyelement)
-- nvl2(anyelement,anyelement,anyelement)
-- trunc(timestamp,text)
-- trunc(timestamptz,text)
-- nanvl(numeric,numeric)
-- regexp_substr(text,text,int4)
-- regexp_substr(text,text,int4,int4,text,int4)
-- regexp_instr(text,text,int4,int4,text,int4)
-- regexp_replace(text,text,text,int4,int4,text)
-- regexp_count(text,text,int4,text)
-- replace(text,text)
-- ora_hash(anyelement,bigint,bigint)
-- show(text)
-- show_parameter(text)
-- dump(anynonarray)


set client_min_messages='warning';


-- =============================================================================
-- Create Schema
-- =============================================================================
do $$
declare
    l_cnt       bigint;
    l_version   varchar(10);
begin
    set client_min_messages='warning';
    select count(*) into l_cnt from pg_catalog.pg_namespace where nspname = 'oracle';
    if l_cnt = 0
    then
        create schema oracle;
    end if;
end;
$$ language plpgsql;


-- =============================================================================
-- Version Table/Function
-- =============================================================================
create table if not exists oracle.compat_version
(
    compat_type     varchar(10),    -- VIEW, FUNCTION
    object_name     varchar(128),   -- Compat object name
    object_version  varchar(10),    -- Compat object version
    constraint pk_compat_version primary key(compat_type, object_name)
);

drop function if exists oracle.drop_compat_function(varchar, varchar, varchar, varchar, varchar);
create or replace function oracle.drop_compat_function( p_object_type    varchar(10)
                                                      , p_object_name    varchar(128)
                                                      , p_object_version varchar(10)
                                                      , p_function_lang  varchar(16)  default 'sql'
                                                      , p_object_schema  varchar(128) default 'pg_catalog')
returns boolean
as $$
declare
    l_version    varchar(10);
    l_language   varchar(16);
    l_result     boolean      := 'true';
    l_operation  varchar(128);
begin
    CREATE temporary table if not exists temp_result
    (
        object_type     varchar(10),
        object_name     varchar(128),
        local_version   varchar(10),
        script_version  varchar(10),
        local_language  varchar(10),
        script_language varchar(10),
        operation       varchar(128)
    );

    select max(object_version) into l_version
      from oracle.compat_version
     where object_name = lower(p_object_name)
       and compat_type = lower(p_object_type);

    if instr(p_object_name, '()') = 0    -- has parameters
    then
        select max(lanname) into l_language
          from (select l.lanname
                     , p.proname || '(' || string_agg(case when a.typname is not null then a.typname||'[]' else t.typname end, ',' order by p.id) || ')' as full_name
                  from (select pronamespace
                             , proname
                             , prolang
                             , proargtypes
                             , generate_series(1, array_length(proargtypes, 1)) as id
                             , unnest(proargtypes) as protype
                          from pg_proc
                         where proname = substr(p_object_name, 1, instr(p_object_name, '(') - 1)
                       ) as p
                  join pg_namespace as n on p.pronamespace = n.oid
                  join pg_language as l on p.prolang = l.oid
                  join pg_type as t on p.protype = t.oid
                  left join pg_type as a on t.typcategory = 'A' and t.typelem = a.oid   -- for array type
                 where n.nspname = p_object_schema
                 group by p.proname, p.proargtypes, l.lanname
               ) as x
         where full_name = p_object_name;
    else    -- no parameter
        select max(l.lanname) into l_language
          from pg_proc as p
          join pg_namespace as n on p.pronamespace = n.oid
          join pg_language as l on p.prolang = l.oid
         where n.nspname = p_object_schema
           and p.proname = replace(p_object_name, '()', '')
           and oidvectortypes(p.proargtypes) is null;
    end if;

    if l_language is null
    then
        l_operation := 'Initial creation';
    elsif l_language != p_function_lang
    then
        l_result = 'false';
        l_operation := 'Skip due to language';
    elsif l_version < p_object_version
    then
        l_operation := 'Upgrade';
    else
        l_result = 'false';
        l_operation := 'Skip due to version';
    end if;

    if l_result
    then
        execute 'drop '||p_object_type||' if exists '||p_object_schema||'.'||p_object_name||' cascade';
        insert into oracle.compat_version
        values (p_object_type, p_object_name, p_object_version)
        ON DUPLICATE KEY UPDATE object_version = p_object_version;
    else
        insert into oracle.compat_version
        values (p_object_type, p_object_name, p_object_version)
        ON DUPLICATE KEY UPDATE NOTHING;
    end if;

    insert into temp_result values ( p_object_type
                                   , p_object_name
                                   , l_version
                                   , p_object_version
                                   , l_language
                                   , p_function_lang
                                   , l_operation);
    return l_result;
end;
$$ language plpgsql;


-- 检测数据库内部的函数带参数的全程（无参数函数不显示）
    -- select l.lanname
    --      , p.proname || '(' || string_agg(case when a.typname is not null then a.typname||'[]' else t.typname end, ',' order by p.id) || ')' as full_name
    --   from (select pronamespace
    --              , proname
    --              , prolang
    --              , proargtypes
    --              , generate_series(1, array_length(proargtypes, 1)) as id
    --              , unnest(proargtypes) as protype
    --           from pg_proc
    --          where proname = 'dump'
    --        ) as p
    --   join pg_namespace as n on p.pronamespace = n.oid
    --   join pg_language as l on p.prolang = l.oid
    --   join pg_type as t on p.protype = t.oid
    --   left join pg_type as a on t.typcategory = 'A' and t.typelem = a.oid
    --  where n.nspname = 'pg_catalog'
    --  group by p.proname, p.proargtypes, l.lanname;




-- =============================================================================
-- All creations are running in ANONYMOUS BLOCK
-- =============================================================================
do $VIEW_CREATION$
declare
    l_cnt    bigint;
begin
    -- =========================================================================
    -- months_between(timestamp,timestamp)
    -- =========================================================================
    if oracle.drop_compat_function('function', 'months_between(timestamp,timestamp)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.months_between(d1 timestamp,d2 timestamp)
        RETURNS integer IMMUTABLE AS $$
        SELECT (extract(year from age(d1,d2))*12 + extract(month from age(d1,d2)))::integer
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- sys_guid()
    -- =========================================================================
    if oracle.drop_compat_function('function', 'sys_guid()', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.sys_guid()
        RETURNS varchar
        AS $$
            select upper(md5(random()::text || clock_timestamp()::text));
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- wm_concat(text)
    -- =========================================================================
    if oracle.drop_compat_function('aggregate', 'wm_concat(text)', '1.0')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.vm_concat_state_func (results text, val text)
        RETURNS text
        LANGUAGE sql COST 50 IMMUTABLE
        AS $$ select results || ',' ||val; $$;

        CREATE OR REPLACE FUNCTION pg_catalog.vm_concat_final_func (results text)
        RETURNS text
        LANGUAGE sql COST 111 IMMUTABLE
        AS $$ select substr(results, 2); $$;

        CREATE AGGREGATE pg_catalog.wm_concat(text)
        (
          sfunc = pg_catalog.vm_concat_state_func,
          stype = text,
          initcond = '',
          FINALFUNC = pg_catalog.vm_concat_final_func
        );
    end if;


    -- =========================================================================
    -- nullif(anyelement,anyelement)
    -- =========================================================================
    -- if oracle.drop_compat_function('function', 'nullif(anyelement,anyelement)', '1.0')
    -- then
    --     CREATE or replace FUNCTION pg_catalog.nullif(p_elem1 anyelement, p_elem2 anyelement)
    --     RETURNS anyelement
    --     AS $$
    --         select case when p_elem1 = p_elem2 then null else p_elem1 end;
    --     $$ LANGUAGE sql;
    -- end if;


    -- =========================================================================
    -- nvl2(anyelement, anyelement,anyelement)
    -- =========================================================================
    if oracle.drop_compat_function('function', 'nvl2(anyelement,anyelement,anyelement)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.nvl2(p_elem anyelement, p_elem_not_null anyelement, p_elem_null anyelement)
        RETURNS anyelement
        IMMUTABLE
        AS $$
            select case when p_elem is null then p_elem_null else p_elem_not_null end;
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- trunc(timestamp,text)
    -- =========================================================================
    if oracle.drop_compat_function('function', 'trunc(timestamp,text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.trunc(p_date timestamp, p_fmt text)
        RETURNS timestamp without time zone
        IMMUTABLE
        AS $$
            select (case upper(p_fmt)
                    -- when '' then 'microseconds'
                    -- when '' then 'milliseconds'
                    when 'SS' then date_trunc('second', p_date)
                    WHEN 'MI' then date_trunc('minute', p_date)
                    WHEN 'HH' then date_trunc('hour', p_date)
                    WHEN 'HH12' then date_trunc('hour', p_date)
                    WHEN 'HH24' then date_trunc('hour', p_date)
                    when 'DAY' then date_trunc('week', p_date) - interval '1 day'  -- 一周的第一天（从周日开始）
                    when 'DY' then date_trunc('week', p_date) - interval '1 day'  -- 一周的第一天（从周日开始）
                    when 'D' then date_trunc('week', p_date) - interval '1 day'  -- 一周的第一天（从周日开始）
                    when 'DDD' then date_trunc('day', p_date)
                    when 'DD' then date_trunc('day', p_date)
                    when 'J' then date_trunc('day', p_date)
                    when 'W' then date_trunc('day', p_date) - interval '1day' * (extract('day' from p_date) % 7 - 1)  -- 历史上最近的与当月1号相同星期时间的时间
                    when 'IW' then date_trunc('week', p_date)  -- 一周的第一天（从周一开始）
                    when 'WW' then date_trunc('day', p_date) - interval '1day' * (extract('doy' from p_date) % 7 - 1)  -- 历史上最近的与当年1号相同星期时间的时间
                    when 'MONTH' then date_trunc('month', p_date)
                    when 'MON' then date_trunc('month', p_date)
                    when 'MM' then date_trunc('month', p_date)
                    when 'RM' then date_trunc('month', p_date)
                    when 'Q' then date_trunc('quarter', p_date)
                    when 'IYYY' then date_trunc('year', p_date) + interval '1day' * (8 - extract('dow' from date_trunc('year', p_date))) % 7  -- 当年第一个周一
                    when 'IYY' then date_trunc('year', p_date) + interval '1day' * (8 - extract('dow' from date_trunc('year', p_date))) % 7  -- 当年第一个周一
                    when 'IY' then date_trunc('year', p_date) + interval '1day' * (8 - extract('dow' from date_trunc('year', p_date))) % 7  -- 当年第一个周一
                    when 'I' then date_trunc('year', p_date) + interval '1day' * (8 - extract('dow' from date_trunc('year', p_date))) % 7  -- 当年第一个周一
                    when 'SYYYY' then date_trunc('year', p_date)
                    when 'YYYY' then date_trunc('year', p_date)
                    when 'YEAR' then date_trunc('year', p_date)
                    when 'SYEAR' then date_trunc('year', p_date)
                    when 'YYY' then date_trunc('year', p_date)
                    when 'YY' then date_trunc('year', p_date)
                    when 'Y' then date_trunc('year', p_date)
                    -- when '' then 'decade'
                    when 'CC' then date_trunc('century', p_date)
                    when 'SCC' then date_trunc('century', p_date)
                    -- when '' then 'millennium'
                    else date_trunc(p_fmt, p_date) end)::timestamp without time zone;
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- trunc(timestamptz,text)
    -- =========================================================================
    if oracle.drop_compat_function('function', 'trunc(timestamptz,text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.trunc(p_date timestamptz, p_fmt text)
        RETURNS timestamp without time zone
        IMMUTABLE
        AS $$
            select (case upper(p_fmt)
                    -- when '' then 'microseconds'
                    -- when '' then 'milliseconds'
                    when 'SS' then date_trunc('second', p_date)
                    WHEN 'MI' then date_trunc('minute', p_date)
                    WHEN 'HH' then date_trunc('hour', p_date)
                    WHEN 'HH12' then date_trunc('hour', p_date)
                    WHEN 'HH24' then date_trunc('hour', p_date)
                    when 'DAY' then date_trunc('week', p_date) - interval '1 day'  -- 一周的第一天（从周日开始）
                    when 'DY' then date_trunc('week', p_date) - interval '1 day'  -- 一周的第一天（从周日开始）
                    when 'D' then date_trunc('week', p_date) - interval '1 day'  -- 一周的第一天（从周日开始）
                    when 'DDD' then date_trunc('day', p_date)
                    when 'DD' then date_trunc('day', p_date)
                    when 'J' then date_trunc('day', p_date)
                    when 'W' then date_trunc('day', p_date) - interval '1day' * (extract('day' from p_date) % 7 - 1)  -- 历史上最近的与当月1号相同星期时间的时间
                    when 'IW' then date_trunc('week', p_date)  -- 一周的第一天（从周一开始）
                    when 'WW' then date_trunc('day', p_date) - interval '1day' * (extract('doy' from p_date) % 7 - 1)  -- 历史上最近的与当年1号相同星期时间的时间
                    when 'MONTH' then date_trunc('month', p_date)
                    when 'MON' then date_trunc('month', p_date)
                    when 'MM' then date_trunc('month', p_date)
                    when 'RM' then date_trunc('month', p_date)
                    when 'Q' then date_trunc('quarter', p_date)
                    when 'IYYY' then date_trunc('year', p_date) + interval '1day' * (8 - extract('dow' from date_trunc('year', p_date))) % 7  -- 当年第一个周一
                    when 'IYY' then date_trunc('year', p_date) + interval '1day' * (8 - extract('dow' from date_trunc('year', p_date))) % 7  -- 当年第一个周一
                    when 'IY' then date_trunc('year', p_date) + interval '1day' * (8 - extract('dow' from date_trunc('year', p_date))) % 7  -- 当年第一个周一
                    when 'I' then date_trunc('year', p_date) + interval '1day' * (8 - extract('dow' from date_trunc('year', p_date))) % 7  -- 当年第一个周一
                    when 'SYYYY' then date_trunc('year', p_date)
                    when 'YYYY' then date_trunc('year', p_date)
                    when 'YEAR' then date_trunc('year', p_date)
                    when 'SYEAR' then date_trunc('year', p_date)
                    when 'YYY' then date_trunc('year', p_date)
                    when 'YY' then date_trunc('year', p_date)
                    when 'Y' then date_trunc('year', p_date)
                    -- when '' then 'decade'
                    when 'CC' then date_trunc('century', p_date)
                    when 'SCC' then date_trunc('century', p_date)
                    -- when '' then 'millennium'
                    else date_trunc(p_fmt, p_date) end)::timestamp without time zone;
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- nanvl(numeric,numeric)
    -- =========================================================================
    if oracle.drop_compat_function('function', 'nanvl(numeric,numeric)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.nanvl(p_num1 numeric, p_num2 numeric)
        RETURNS numeric
        IMMUTABLE
        AS $$
            select case when p_num1 = 'NaN' then p_num2 else p_num1 end;
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- regexp_substr(text,text,int4)
    -- =========================================================================
    if oracle.drop_compat_function('function', 'regexp_substr(text,text,int4)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.regexp_substr ( p_source    text
                                                            , p_pattern   text
                                                            , p_position  int)
        RETURNS text
        IMMUTABLE
        AS $$
            select regexp_substr(case when p_position > 1 then substr(p_source, p_position) else p_source end, p_pattern);
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- regexp_substr(text,text,int4,int4,text,int4)
    -- 注意： p_flag 只支持 Oracle 中的 i 和 c 模式，分别表示：
    --   i = 大小写不敏感
    --   c = 大小写敏感，默认模式
    -- 测试用例：
    --   SELECT regexp_substr('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)', 5, 1, 'c') from dual;
    --   SELECT regexp_substr('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)', 5, 1, 'i') from dual;

    --   SELECT regexp_substr('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)', 1, 1, 'i') from dual;
    --   SELECT regexp_substr('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)', 1, 2, 'i') from dual;
    --   SELECT regexp_substr('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)', 5, 2, 'i') from dual;

    --   SELECT regexp_substr('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)', 5, 2, 'i', -1) from dual;
    --   SELECT regexp_substr('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)', 5, 2, 'i', 0) from dual;
    --   SELECT regexp_substr('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)', 5, 2, 'i', 1) from dual;
    --   SELECT regexp_substr('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)', 5, 2, 'i', 2) from dual;
    --   SELECT regexp_substr('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)', 5, 2, 'i', 3) from dual;
    -- =========================================================================
    if oracle.drop_compat_function('function', 'regexp_substr(text,text,int4,int4,text,int4)', '1.0', 'plpgsql')
    then
        CREATE or replace FUNCTION pg_catalog.regexp_substr ( p_source     text
                                                            , p_pattern    text
                                                            , p_position   int
                                                            , p_occurrence int
                                                            , p_flag       text default 'c'
                                                            , p_subexpr    int  default 0)
        RETURNS text
        IMMUTABLE
        AS $$
        declare
            l_result    text;
        begin
            if p_position < 1
            then
                raise exception 'ERROR: Argument "p_position" must be greater than 0  (p_position [%] > 0)', p_position;
            elsif p_occurrence < 1
            then
                raise exception 'ERROR: Argument "p_occurrence" must be greater than 0 (p_occurrence [%] > 0)', p_occurrence;
            elsif p_subexpr < 0
            then
                raise exception 'ERROR: Argument "p_subexpr" must not be smaller than 0 (p_subexpr [%] >= 0)', p_subexpr;
            end if;
            select max(match_array[p_subexpr + 1]) into l_result
              from (SELECT rownum as id
                         , regexp_matches( substr(p_source, p_position)
                                         , '('||p_pattern||')'
                                         , case lower(p_flag) when 'i' then 'gi' else 'g' end
                                         ) as match_array
                      from dual
                   ) as t
             where id = p_occurrence;

            return l_result;
        end;
        $$ LANGUAGE plpgsql;
    end if;


    -- =========================================================================
    -- regexp_instr(text,text,int4,int4,int4,text,int4)
    -- 注意： p_flag 只支持 Oracle 中的 i 和 c 模式，分别表示：
    --   i = 大小写不敏感
    --   c = 大小写敏感，默认模式
    -- 注意： 最后一个参数 p_subexpr 暂时不支持，即不能确定匹配字符串中某个子串的位置
    --
    -- 测试用例：
    --   SELECT regexp_instr('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)', 5, 1, 0, 'c') as c1
    --        , regexp_instr('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)', 5, 1, 0, 'i') as c2
    --        , regexp_instr('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)', 5, 1, 1, 'i') as c3
    --     from dual;

    --   SELECT regexp_instr('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)', 1, 1, 0, 'i') as c1
    --        , regexp_instr('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)', 1, 2, 0, 'i') as c2
    --        , regexp_instr('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)', 5, 2, 0, 'i') as c3
    --     from dual;

    --   SELECT regexp_instr('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)', 5, 2, 0, 'i', -1) from dual;
    --   SELECT regexp_instr('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)', 5, 2, 0, 'i', 0) from dual;
    --   SELECT regexp_instr('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)', 5, 2, 0, 'i', 1) from dual;
    --   SELECT regexp_instr('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)', 5, 2, 0, 'i', 2) from dual;
    --   SELECT regexp_instr('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)', 5, 2, 0, 'i', 3) from dual;

    -- 以下测试用例在 OpenGauss 中与 Oracle 有差异：
    --   Oracle 返回第二个 Oracle 的位置： 8, 14, 21, 21
    --   OpenGauss 中由 instr 返回的是第一个满足的位置： 8, 8, 8, 8
    -- SELECT regexp_instr('Oracle.begin.Oracle.Oracle.end', 'begin.*(Oracle).*(Oracle)', 1, 1, 0, 'i', 0) as c1
    --      , regexp_instr('Oracle.begin.Oracle.Oracle.end', 'begin.*(Oracle).*(Oracle)', 1, 1, 0, 'i', 1) as c1
    --      , regexp_instr('Oracle.begin.Oracle.Oracle.end', 'begin.*(Oracle).*(Oracle)', 1, 1, 0, 'i', 2) as c2
    --      , regexp_instr('Oracle.begin.Oracle.Oracle.end', 'begin.*Oracle.*(Oracle)', 1, 1, 0, 'i', 1) as c3
    --   from dual;

    -- SELECT regexp_instr('begin.Oracle.Oracle.end', 'Or.', 1, 1, 0, 'i') as c1
    --      , regexp_instr('begin.Oracle.Oracle.end', 'Or.', 1, 2, 0, 'i') as c2
    --      , regexp_instr('begin.Oracle.Orbcle.end', 'Or.', 1, 1, 0, 'i') as c3
    --      , regexp_instr('begin.Oracle.Orbcle.end', 'Or.', 1, 2, 0, 'i') as c4
    --   FROM DUAL;
    -- =========================================================================
    if oracle.drop_compat_function('function', 'regexp_instr(text,text,int4,int4,int4,text,int4)', '1.0', 'plpgsql')
    then
        CREATE or replace FUNCTION pg_catalog.regexp_instr ( p_source     text
                                                           , p_pattern    text
                                                           , p_position   int
                                                           , p_occurrence int
                                                           , p_return_opt int  default 0
                                                           , p_flag       text default 'c'
                                                           , p_subexpr    int  default 0)
        RETURNS int
        IMMUTABLE
        AS $$
        declare
            l_result    int   := 0;
        begin
            if p_position < 1
            then
                raise exception 'ERROR: Argument "p_position" must be greater than 0  (p_position [%] > 0)', p_position;
            elsif p_occurrence < 1
            then
                raise exception 'ERROR: Argument "p_occurrence" must be greater than 0 (p_occurrence [%] > 0)', p_occurrence;
            elsif p_return_opt < 0
            then
                raise exception 'ERROR: Argument "p_return_opt" must not be smaller than 0 (p_return_opt [%] >= 0)', p_return_opt;
            elsif p_subexpr < 0
            then
                raise exception 'ERROR: Argument "p_subexpr" must not be smaller than 0 (p_subexpr [%] >= 0)', p_subexpr;
            end if;
            select case p_return_opt
                   -- p_return_opt = 0, 匹配字符串开头位置
                   when 0 then instr(repeat(' ', p_position - 1)
                                   ||pg_catalog.regexp_replace(source_str,
                                                               p_pattern,
                                                               signal_str||'\&'||signal_str,
                                                               case lower(p_flag) when 'i' then 'gi' else 'g' end
                                                              ),
                                     signal_str,
                                     p_position ,
                                     p_occurrence * 2 - 1
                                    ) - (2 * (p_occurrence - 1)) * 3
                   -- p_return_opt != 0, 匹配字符串结束位置的下一个位置
                   else instr(repeat(' ', p_position - 1)
                            ||pg_catalog.regexp_replace(source_str,
                                                        p_pattern,
                                                        signal_str||'\&'||signal_str,
                                                        case lower(p_flag) when 'i' then 'gi' else 'g' end
                                                       ),
                              signal_str,
                              p_position ,
                              p_occurrence * 2
                             ) - (2 * p_occurrence - 1) * 3
                   end into l_result
              from (select substr(p_source, p_position) as source_str
                         , chr(30)||chr(26)||chr(25) as signal_str) as t;
            return coalesce(l_result, 0);
        end;
        $$ LANGUAGE plpgsql;
    end if;


    -- =========================================================================
    -- regexp_replace(text,text,text,int4,int4,text)
    -- 注意： p_flag 只支持 Oracle 中的 i 和 c 模式，分别表示：
    --   i = 大小写不敏感
    --   c = 大小写敏感，默认模式
    --
    -- 测试用例：
    --   select regexp_replace('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)', 'XXX', 5, 0, 'i')
    --        , regexp_replace('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)', '#\1#', 5, 0, 'i')
    --        , regexp_replace('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)', 'XXX', 5, 1, 'i')
    --        , regexp_replace('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)', 'XXX', 5, 2, 'i')
    --        , regexp_replace('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)', 'XXX', 5, 3, 'i')
    --     from dual;
    -- =========================================================================
    if oracle.drop_compat_function('function', 'regexp_replace(text,text,text,int4,int4,text)', '1.0', 'plpgsql')
    then
        CREATE or replace FUNCTION pg_catalog.regexp_replace ( p_source      text
                                                             , p_pattern     text
                                                             , p_replacement text
                                                             , p_position    int
                                                             , p_occurrence  int  default 0
                                                             , p_flag        text default 'c')
        RETURNS text
        IMMUTABLE
        AS $$
        declare
            l_result    text   := 0;
        begin
            if p_position < 1
            then
                raise exception 'ERROR: Argument "p_position" must be greater than 0  (p_position [%] > 0)', p_position;
            elsif p_occurrence < 0
            then
                raise exception 'ERROR: Argument "p_occurrence" must not be smaller than 0 (p_occurrence [%] >= 0)', p_occurrence;
            end if;

            if p_occurrence = 0
            then
                select pre_str
                    || regexp_replace(source_str,
                                      p_pattern,
                                      p_replacement,
                                      case lower(p_flag) when 'i' then 'gi' else 'g' end
                                     )
                  into l_result
                  from (select substr(p_source, 1, p_position - 1) as pre_str
                             , substr(p_source, p_position) as source_str
                             , chr(30)||chr(26)||chr(25) as signal_str) as t;
            else
                select string_agg(case when id % 3 = 2 and p_occurrence = round(id/3.0) then ''  -- 原始值的取舍
                                       when id % 3 = 0 and p_occurrence != round(id/3.0) then ''  -- 替换值的取舍
                                       else partial_str
                                  end,
                                  ''
                                 ) into l_result
                  from (select rownum as id
                             , unnest(string_to_array( pre_str
                                                    || regexp_replace(source_str,
                                                                      p_pattern,
                                                                      signal_str||'\&'||signal_str||p_replacement||signal_str,  -- 同时保留原字符串与替换后的字符串，以便在外层再按照 p_occurrence 进行选择
                                                                      case lower(p_flag) when 'i' then 'gi' else 'g' end
                                                                     ),
                                                       signal_str
                                                     )
                                     ) as partial_str
                          from (select substr(p_source, 1, p_position - 1) as pre_str
                                     , substr(p_source, p_position) as source_str
                                     , chr(30)||chr(26)||chr(25) as signal_str) as t) as x;
            end if;
            return l_result;
        END;
        $$ LANGUAGE plpgsql;
    end if;


    -- =========================================================================
    -- regexp_count(text,text,int4,text)
    -- 注意： p_flag 只支持 Oracle 中的 i 和 c 模式，分别表示：
    --   i = 大小写不敏感
    --   c = 大小写敏感，默认模式
    --
    -- 测试用例：
    --   select regexp_count('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)') as c1
    --        , regexp_count('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)', 1, 'i') as c2
    --        , regexp_count('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)', 5) as c3
    --        , regexp_count('foobarbequebazilbarfbonk', '(b[^b]+)(B[^b]+)', 5, 'i') as c4
    --     from dual;
    -- =========================================================================
    if oracle.drop_compat_function('function', 'regexp_count(text,text,int4,text)', '1.0', 'sql')
    then
        CREATE or replace FUNCTION pg_catalog.regexp_count ( p_source      text
                                                           , p_pattern     text
                                                           , p_position    int  default 1
                                                           , p_flag        text default 'c')
        RETURNS int
        IMMUTABLE
        AS $$
        select length(pg_catalog.regexp_replace(p_source, p_pattern, '#\&', case lower(p_flag) when 'i' then 'gi' else 'g' end))
             - length(p_source);
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- replace(text,text)
    -- =========================================================================
    if oracle.drop_compat_function('function', 'replace(text,text)', '1.0', 'sql')
    then
        CREATE or replace FUNCTION pg_catalog.replace ( p_source      text
                                                      , p_pattern     text)
        RETURNS text
        IMMUTABLE
        AS $$
        select pg_catalog.replace(p_source, p_pattern, '');
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- ora_hash(anyelement,int4,int4)
    -- 测试用例：
    --   select ora_hash(relname, 10) from pg_class limit 10;
    -- =========================================================================
    if oracle.drop_compat_function('function', 'ora_hash(anyelement,int4,int4)', '1.0', 'plpgsql')
    then
        CREATE or replace FUNCTION pg_catalog.ora_hash ( p_data     anyelement
                                                       , p_buckets  int        default 0
                                                       , p_seed     int        default 0)
        RETURNS int
        AS $$
        declare
            l_data_type  text;
        begin
            l_data_type := pg_typeof(p_data)::text;
            if    l_data_type = 'aclitem'                     then return abs(mod(hash_aclitem(p_data) + p_seed, p_buckets) + 1);
            elsif l_data_type = 'anyarray'                    then return abs(mod(hash_array(p_data) + p_seed, p_buckets) + 1);
            elsif l_data_type = 'numeric'                     then return abs(mod(hash_numeric(p_data) + p_seed, p_buckets) + 1);
            elsif l_data_type = 'anyrange'                    then return abs(mod(hash_range(p_data) + p_seed, p_buckets) + 1);
            elsif l_data_type = 'character'                   then return abs(mod(hashbpchar(p_data) + p_seed, p_buckets) + 1);
            elsif l_data_type = '"char"'                      then return abs(mod(hashchar(p_data) + p_seed, p_buckets) + 1);
            elsif l_data_type = 'anyenum'                     then return abs(mod(hashenum(p_data) + p_seed, p_buckets) + 1);
            elsif l_data_type = 'real'                        then return abs(mod(hashfloat4(p_data) + p_seed, p_buckets) + 1);
            elsif l_data_type = 'double precision'            then return abs(mod(hashfloat8(p_data) + p_seed, p_buckets) + 1);
            elsif l_data_type = 'inet'                        then return abs(mod(hashinet(p_data) + p_seed, p_buckets) + 1);
            elsif l_data_type = 'tinyint'                     then return abs(mod(hashint1(p_data) + p_seed, p_buckets) + 1);
            elsif l_data_type = 'smallint'                    then return abs(mod(hashint2(p_data) + p_seed, p_buckets) + 1);
            elsif l_data_type = 'int2vector'                  then return abs(mod(hashint2vector(p_data) + p_seed, p_buckets) + 1);
            elsif l_data_type = 'integer'                     then return abs(mod(hashint4(p_data) + p_seed, p_buckets) + 1);
            elsif l_data_type = 'bigint'                      then return abs(mod(hashint8(p_data) + p_seed, p_buckets) + 1);
            elsif l_data_type = 'macaddr'                     then return abs(mod(hashmacaddr(p_data) + p_seed, p_buckets) + 1);
            elsif l_data_type = 'name'                        then return abs(mod(hashname(p_data) + p_seed, p_buckets) + 1);
            elsif l_data_type = 'oid'                         then return abs(mod(hashoid(p_data) + p_seed, p_buckets) + 1);
            elsif l_data_type = 'oidvector'                   then return abs(mod(hashoidvector(p_data) + p_seed, p_buckets) + 1);
            elsif l_data_type = 'text'                        then return abs(mod(hashtext(p_data) + p_seed, p_buckets) + 1);
            elsif l_data_type = 'internal'                    then return abs(mod(hashvarlena(p_data) + p_seed, p_buckets) + 1);
            elsif l_data_type = 'interval'                    then return abs(mod(interval_hash(p_data) + p_seed, p_buckets) + 1);
            elsif l_data_type = 'smalldatetime'               then return abs(mod(smalldatetime_hash(p_data) + p_seed, p_buckets) + 1);
            elsif l_data_type = 'time without time zone'      then return abs(mod(time_hash(p_data) + p_seed, p_buckets) + 1);
            elsif l_data_type = 'timestamp without time zone' then return abs(mod(timestamp_hash(p_data) + p_seed, p_buckets) + 1);
            elsif l_data_type = 'time with time zone'         then return abs(mod(timetz_hash(p_data) + p_seed, p_buckets) + 1);
            elsif l_data_type = 'uuid'                        then return abs(mod(uuid_hash(p_data) + p_seed, p_buckets) + 1);
            else
                raise exception 'Unsupported data type [%]', l_data_type;
            end if;
        end;
        $$ LANGUAGE plpgsql;
    end if;


    -- =========================================================================
    -- show(text)
    -- 测试用例：
    --   select show('client');
    --   select show('log');
    -- =========================================================================
    if oracle.drop_compat_function('function', 'show(text)', '1.0', 'sql')
    then
        CREATE or replace FUNCTION pg_catalog.show (p_keyword  text)
        RETURNS setof text
        IMMUTABLE
        AS $$
        with matched_params as (select name
                                     , setting||case when '('||unit||')' = '()' then '' else ' ('||unit||')' end as setting
                                     , short_desc
                                  from pg_settings
                                 where name like '%'||p_keyword||'%'
                               ),
             max_size as (select max(lengthb(name)) as name_len
                               , max(lengthb(setting)) as setting_len
                               , max(lengthb(short_desc)) as desc_len
                            from matched_params
                         )
        select rpad('name', name_len)||' | '||rpad('setting', setting_len)||' | '||'description'
          from max_size
         union all
        select rpad('-', name_len, '-')||'-+-'||rpad('-', setting_len, '-')||'-+-'||rpad('-', desc_len, '-')
          from max_size
         union all
        select rpad(name, name_len)||' | '||rpad(setting, setting_len)||' | '||short_desc
          from matched_params, max_size;
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- show_parameter(text)
    -- 测试用例：
    --   select show_parameter('client');
    --   select show_parameter('log');
    -- =========================================================================
    if oracle.drop_compat_function('function', 'show_parameter(text)', '1.0', 'sql')
    then
        CREATE or replace FUNCTION pg_catalog.show_parameter (p_keyword  text)
        RETURNS setof text
        IMMUTABLE
        AS $$
        with matched_params as (select name
                                     , setting||case when '('||unit||')' = '()' then '' else ' ('||unit||')' end as setting
                                     , short_desc
                                  from pg_settings
                                 where name like '%'||p_keyword||'%'
                               ),
             max_size as (select max(lengthb(name)) as name_len
                               , max(lengthb(setting)) as setting_len
                               , max(lengthb(short_desc)) as desc_len
                            from matched_params
                         )
        select rpad('name', name_len)||' | '||rpad('setting', setting_len)||' | '||'description'
          from max_size
         union all
        select rpad('-', name_len, '-')||'-+-'||rpad('-', setting_len, '-')||'-+-'||rpad('-', desc_len, '-')
          from max_size
         union all
        select rpad(name, name_len)||' | '||rpad(setting, setting_len)||' | '||short_desc
          from matched_params, max_size;
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- dump(anynonarray)
    -- 参考： http://blog.itpub.net/6906/viewspace-2653368/
    -- 测试用例：
    --   select dump('红叶'::text);
    --   select dump('Hongye'::text);
    -- =========================================================================
    if oracle.drop_compat_function('function', 'dump(anynonarray)', '1.0', 'plpgsql')
    then
        CREATE OR REPLACE FUNCTION pg_catalog.dump(anynonarray)
        RETURNS TEXT
        IMMUTABLE
        AS $$
        DECLARE
            v_hexstr    TEXT;
            v_hexbyte   TEXT;
            v_tmp       TEXT;
            i           INT;
            v_len       INT;
        BEGIN
            SELECT octet_length($1) into v_len;
            v_hexstr := 'Len=' || v_len || ' ';
            v_tmp := ',';
            FOR i in 1..v_len LOOP
                select to_hex(get_byte($1::bytea, i-1)) into v_hexbyte;
                if i = v_len then
                    v_tmp := '';
                end if;
                v_hexstr := v_hexstr || v_hexbyte || v_tmp;
            END LOOP;
            RETURN v_hexstr;
        END;
        $$ LANGUAGE plpgsql;
    end if;

end;
$VIEW_CREATION$ language plpgsql;

-- Show result & Exit
select * from temp_result where exists (select 1 from pg_settings where name = 'application_name' and setting = 'gsql');
\q

